import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;


public class Assignment2 {
    
    /* The database connection for this session. */
    Connection connection;

    /* Identifies the postgreSQL driver using Class.forName method. */
    Assignment2() {
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException ex) {
        }	
    }
	
    /* Using the String input parameters which are the URL, username, and 
     * password respectively, establish the Connection to be used for this 
     * session. Returns true if the connection was successful. */
    boolean connectDB(String URL, String username, String password) {
        try {
            connection = DriverManager.getConnection("jdbc:postgresql://" + URL 
                    + ":5432/csc343h-" + username, username, password);
        } catch (SQLException ex) {
        	System.out.println(ex.getMessage());
            System.out.println("connection failed.");
        }
        
        if (connection != null) {
            return true;
        } else {
            return false;
        }
    }
    
    /* Closes the connection. Returns true if the closure was successful. */
    boolean disconnectDB() {
        try {
            connection.close();
        } catch (SQLException ex) {
            return false;
        }
        return true;
    }
    
    /* Inserts a row into the student table. dname is the name of the department.
     * You have to check if the department exists, if the sex is one of the two 
     * values ('M' or 'F') and if the year of study is a valid number (>0 && < 6).
     * Returns true if the insertion was successful, false otherwise. */ 
    boolean insertStudent(int sid, String lastName, String firstName, int age,
                          String sex, String dname, int yearOfStudy) {
        // Validate input value
        if (!(sex.equals("M") || sex.equals("F"))) {
            return false;
        } else if (yearOfStudy<1 || yearOfStudy>5) {
            return false;
        } else if (!checkDept(dname)) {
            return false;
        } 
        
        String dcode = getDcodeByDname(dname);
        
        try {
            // Run the insertion
            Statement stmt = connection.createStatement();
            String sqlText = "INSERT INTO student "
                    + "VALUES (" + sid + ", '" + lastName + "', '" + firstName 
                    + "', '" + sex + "', " + age + ", '" + dcode + "', " 
                    + yearOfStudy + ");";
            stmt.executeUpdate(sqlText);
            stmt.close();
                    
        } catch (SQLException ex) {
            return false;        
        }
        
        return true;
    }
    
    /* Return true if the dname is in the department table. */
    boolean checkDept(String dname)  {
        
        boolean res = false;
        
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select * from department"
                + " where dname = " + "'" + dname + "'");
            
            // find out if the ResultSet contains no row.
            res = rs.next();
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
        }
        
        return res;
    }
    
    /* Given dname, find the dcode from department table. */
    String getDcodeByDname(String dname) {
        
        String res = null;
        
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select dcode from department where"
                    + " dname = '" + dname + "'");
            
            if (rs != null && rs.next()) {
                res = rs.getString("dcode");
            }
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
        }
        
        return res;
    }
    
    /* Returns the number of students in department dname. Returns -1 if an 
     * error occurs. */
    int getStudentsCount(String dname) {
        
        int res = 0;
        
        
        try {
            Statement stmt = connection.createStatement();
            String sqlText = "select count (distinct s.sid) as cnt"
                           + " from department d, student s "
                           + "where s.dcode = d.dcode "
                           + "and d.dname = '" + dname + "';";
            ResultSet rs = stmt.executeQuery(sqlText);
            
            if (rs != null && rs.next()) {
                res = rs.getInt("cnt");
            } else {
                res = -1;
            }
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            res = -1;
        }
        return res;
    }
    
    /* Returns a string with student information of student with student id sid.
     * The output is ?firstName:lastName:sex:age:yearOfStudy:department?. 
     * Returns an empty string ?? if the student does not exist. */
    String getStudentInfo(int sid) {
        
        String res = null;
        
        try {
            Statement stmt = connection.createStatement();
            String sqlText = "select sfirstName, slastName, sex, age, yearofstudy, dcode "
                           + "from student "
                           + "where sid = " + sid;
            ResultSet rs = stmt.executeQuery(sqlText);
            
            if (rs != null && rs.next()) {
                res = rs.getString(1) + ":" + rs.getString(2) + ":" 
                    + rs.getString(3) + ":" + rs.getInt(4) + ":" + rs.getInt(5)
                    + ":" + rs.getString(6);
            } else {
                res = "";
            }
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            res = "";
        }

        return res;
    }
    
    /* Changes the department name to the department name supplied (newName). 
     * Accepts the dcode and new department name as Strings (in that order). 
     * Returns true if the change was successful, false otherwise. */
    boolean chgDept(String dcode, String newName) {
        
        boolean res = false;
        
        try {
            Statement stmt = connection.createStatement();
            
            // count the number of column need to be updated.
            String numText = "select count(*) as cnt "
            			   + "from department "
            			   + "where dcode = '" + dcode + "'";
            ResultSet rs = stmt.executeQuery(numText);
            rs.next();
            int num = rs.getInt(1);
            
            String sqlText = "update department "
                           + "set dname = '" + newName + "'"
                           + "where dcode = '" + dcode + "'";
            int numUpdate = stmt.executeUpdate(sqlText);
            
            if (num == numUpdate) res = true;
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
        }
        return res;
    }
    
    /* Deletes the department identified by the input String dcode. Returns true
     * if the deletion was successful, false otherwise. */
    boolean deleteDept(String dcode) {
        boolean res = false;
        try {
            Statement stmt = connection.createStatement();
            
            // count the number of column need to be updated.
            String numText = "select count(*) as cnt "
            			   + "from department "
            			   + "where dcode = '" + dcode + "'";
            ResultSet rs = stmt.executeQuery(numText);
            rs.next();
            int num = rs.getInt(1);
            
            String textD = "delete from department "
                         + "where dcode = '" + dcode + "'";
            String textC = "delete from course "
                         + "where dcode = '" + dcode + "'";
            String textI = "delete from instructor "
                         + "where dcode = '" + dcode + "'";
            String textS = "delete from student "
                         + "where dcode = '" + dcode + "'";
            String textCS = "delete from courseSection "
                          + "where dcode = '" + dcode + "'";
            String textSC1 = "delete from studentCourse sc "
                           + "where sc.csid in (select cs.csid from courseSection cs where cs.dcode = '" + dcode + "')";
            String textSC2 = "delete from studentCourse sc "
                           + "where sc.sid in (select s.sid from student s where s.dcode = '" + dcode + "')";
            String textP = "delete from prerequisites "
                         + "where dcode = '" + dcode + "' "
                         + "or    pdcode = '" + dcode + "'";
            
            stmt.executeUpdate(textSC1);
           // System.out.println("del sc1");
            stmt.executeUpdate(textSC2);
           // System.out.println("del sc2");
            stmt.executeUpdate(textCS);
           // System.out.println("del cs");
            stmt.executeUpdate(textP);
           // System.out.println("del p");
            stmt.executeUpdate(textS);
         //   System.out.println("del s");
            stmt.executeUpdate(textI);
       //     System.out.println("del i");
            stmt.executeUpdate(textC);
     //       System.out.println("del c");
            int numUpdate = stmt.executeUpdate(textD);
//            System.out.println("del prq");
            
            if (num == numUpdate) 
            	res = true;
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
        }
        return res;
    }
    
    /* Returns a string with all the courses a student with student id sid has 
     * taken. The list of courses should follow the contiguous format described
     * above, and contain the following attributes in the order shown:
        ?courseName1:department:semester:year:grade#courseName2:department:semester:year:grade#... ?
     * Returns an empty string ?? if the student does not exist. */
    String listCourses(int sid) {
        
        String res = "";
        
        try {
            Statement stmt = connection.createStatement();
            String sqlText = "select c.cname, d.dname, cs.semester, cs.year, sc.grade "
                           + "from course c, courseSection cs, studentCourse sc, department d "
                           + "where sc.sid = " + sid + " "
                           + "and   sc.csid = cs.csid "
                           + "and   cs.cid = c.cid "
                           + "and   cs.dcode = c.dcode "
                           + "and   c.dcode = d.dcode";
            ResultSet rs = stmt.executeQuery(sqlText);
            
            if (rs != null){
                while(rs.next()){
                    res = res + rs.getString(1) + ":" + rs.getString(2) + ":" 
                        + rs.getInt(3) + ":" + rs.getInt(4) + ":" + rs.getInt(5)
                        + "#";
                }
            }
            // Remove the last # sign
            if (res != ""){
            	res = res.substring(0, res.length()-1);
            }
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {        
        }
        return res;
    }
    
    /* Increases the grades of all the students who took a course in the course 
     * section identified by csid by 10% :) Returns true if the update was 
     * successful, false otherwise. Do not not allow marks to go over 100%. */
    boolean updateGrades(int csid) {
        
        boolean res = false;
        try {
            Statement stmt = connection.createStatement();
            
            // count the number of column need to be updated.
            String numText = "select count(*) as cnt "
            			   + "from studentCourse "
            			   + "where grade <> -1" 
            			   + " and  csid = " + csid;
            ResultSet rs = stmt.executeQuery(numText);
            rs.next();
            int num = rs.getInt(1);
            
            String sqlText = "update studentCourse "
                           + "set grade = grade * 1.1 "
                           + "where csid = " + csid
                           + " and  grade <> -1";
            String sqlText2 = "update studentCourse "
                            + "set grade = 100 "
                            + "where grade > 100";
            
            int numUpdate = stmt.executeUpdate(sqlText);
            stmt.executeUpdate(sqlText2);
            
            if (num == numUpdate) res = true;
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
        }
        
        return res;
    }
    
    /* Execute query 7 (highest and lowest average department marks) described 
     * in the Interactive SQL section above. Instead of inserting the results in
     * a table, return them as a String in the same format as is specified for 
     * the output table for the query. Be sure to follow the prestated String 
     * rules involving colons and pound-signs for your return String. Do not use
     * the views that you created in the SQL part. If you need views, create 
     * them when you execute function query7(). */
    String query7() {
        
        String res = "";
        try {
            Statement stmt = connection.createStatement();
            String createV = "create table v7(csid integer not null, avgmark integer not null);"; 
            String insertV = "insert into v7 (SELECT	cs2.csid, AVG(sc2.grade) AS avgmark"
            			   + "       				 FROM	department d2, courseSection cs2, studentCourse sc2"
                           + "           			 WHERE	d2.dname = 'Computer Science'"
                           + "        				 AND       d2.dcode = cs2.dcode"
                           + "        				 AND	cs2.csid = sc2.csid"
                           + "        				 AND 	sc2.grade <> -1"
                           + "        				 GROUP BY  cs2.csid"
                           + "        				 HAVING	COUNT(distinct sc2.sid) >= 3);";
            stmt.executeUpdate(createV);
            stmt.executeUpdate(insertV);
            String sqlText = "SELECT c1.cname, cs1.semester, cs1.year, v.avgmark "
                           + "FROM   course c1, courseSection cs1, v7 v "
                           + "WHERE   v.csid = cs1.csid "
                           + "AND     cs1.cid = c1.cid "
                           + "AND     (v.avgmark IN (SELECT MAX(avgmark) FROM v7) OR v.avgmark IN (SELECT MIN(avgmark) FROM v7))";
					
            ResultSet rs = stmt.executeQuery(sqlText);
            if (rs != null) {
                while (rs.next()) {
                    res = res + rs.getString(1) + ":" + rs.getInt(2) + ":"
                        + rs.getInt(3) + ":" + rs.getInt(4) + "#";
                }
            }
            if (res != ""){
            	res = res.substring(0, res.length()-1);
            }
            
            String dropV = "drop table v7;";
            stmt.executeUpdate(dropV);
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
        }
        
        return res;
    }
    
    /* Create a table containing all the female students in the ?Computer Science?
     * department who are in their fourth year of study. The name of the table 
     * is femaleStudents and the attibutes are:
     *       sid INTEGER (student id)
     *       fname CHAR (20) (first name)
     *       lname CHAR (20) (last name)
     * Returns true if the database was successfully updated, false otherwise. */
    boolean updateDB() {
        
        boolean res = false;
        try {
            // Create table
            Statement stmt = connection.createStatement();
            String createTable = "create table femaleStudents ("
                               + "       sid     INTEGER PRIMARY KEY,"
                               + "       fname   CHAR(20) NOT NULL,"
                               + "       lname   CHAR(20) NOT NULL)";
            
            stmt.executeUpdate(createTable);

            // Run the query
            String query = "select s.sid, s.sfirstname, s.slastname "
                         + "from   student s, department d "
                         + "where  s.sex = 'F' "
                         + "and    s.dcode = d.dcode "
                         + "and    d.dname = 'Computer Science' "
                         + "and    s.yearofstudy = 4";
            ResultSet rs = stmt.executeQuery(query);

            // Create prepared statement and insert into table
            String insert = "insert into femaleStudents "
                          + "values (?, ?, ?)";  
            PreparedStatement ps = connection.prepareStatement(insert);
            if (rs != null) {
                while (rs.next()) {
                    ps.setInt(1, rs.getInt(1));
                    ps.setString(2, rs.getString(2));
                    ps.setString(3, rs.getString(3));
                    
                    ps.executeUpdate();
                }
            }
            
            res = true;
            ps.close();
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
        }
        
        return res;
    }
}

